<?php

namespace W3\Db;

use W3\Db;

/**
 * 数据库查询语句构建类
 *
 * @author edikud
 * @date 2022/10/22
 * @copyright Copyright (c) 2022 W3 (http://www.mcooo.com)
 * @license GNU General Public License 2.0
 */
class Query
{
    /** 数据库关键字 */
    const KEYWORDIRECTORY_SEPARATOR = '*PRIMARY|AND|OR|LIKE|BINARY|BY|DISTINCT|AS|IN|IS|NULL';
	
    /**
     * 数据库连接器对象
     *
     * @var object
     */
    public $db;

    /**
     * 查询语句预结构,由数组构成,方便组合为SQL查询字符串
     *
     * @var array
     */
    private $sql;

    /**
     * @var array
     */
    private $params = [];

    /**
     * 默认字段 
     * 
     * @var array
     * @access private
     */
    private static $paramsDefault = [
        'join'   => [],
        'where'  => [],
        'group'  => [],
        'having' => [],
		'order'  => [],
        'rows'   => [],
		'sql'    => [],
    ];

    /**
     * 默认字段 
     * 
     * @var array
     * @access private
     */
    private static $default = [
        'action' => NULL,
        'table'  => NULL,
        'fields' => '*',
        'join'   => NULL,
        'where'  => NULL,
        'limit'  => NULL,
        'offset' => NULL,
        'order'  => NULL,
        'group'  => NULL,
        'having' => NULL,
		'sql'    => NULL,
        'rows'   => [],
    ];

    /**
     * 前缀
     *
     * @var string
     */
    public $prefix;

    /**
     * driver
     *
     * @var OBJ
     */
    public $driver;
	
    /**
     * 构造函数,引用数据库适配器作为内部数据
     *
     * @param Db $db 数据库适配器
     */
    public function __construct(Db $db)
    {
		$this->sql = static::$default;
		$this->params = static::$paramsDefault;
        $this->db = $db;
		$this->prefix = $this->db->prefix();
    }

    /**
     * 过滤数组键值
     *
     * @access private
     * @param string $str 待处理字段值
     * @return string
     */
    public function filter(string $str): string
    {
        $str = $str . ' 0';
        $length = strlen($str);
        $lastIsAlnum = false;
        $result = '';
        $word = '';
        $split = '';
        $quotes = 0;

        for ($i = 0; $i < $length; $i ++) {
            $cha = $str[$i];
            if (ctype_alnum($cha) || false !== strpos('_*', $cha)) {
                if (!$lastIsAlnum) {
                    if ($quotes > 0 && !ctype_digit($word) && '.' != $split
                    && false === strpos(self::KEYWORDIRECTORY_SEPARATOR, strtoupper($word))) {
                        $word = $this->db->adapter()->quote($word);
                    } else if ('.' == $split && 'table' == $word) {
                        $word = $this->prefix;
                        $split = '';
                    }

                    $result .= $word . $split;
                    $word = '';
                    $quotes = 0;
                }

                $word .= $cha;
                $lastIsAlnum = true;
            } else {

                if ($lastIsAlnum) {
                    if (0 == $quotes) {
                        if (false !== strpos(' ,)=<>.+-*/', $cha)) {
                            $quotes = 1;
                        } else if ('(' == $cha) {
                            $quotes = -1;
                        }
                    }

                    $split = '';
                }

                $split .= $cha;
                $lastIsAlnum = false;
            }
        }

        return $result;
    }

    /**
     * set default params
     *
     * @param array $default
     */
    public static function default(array $default)
    {
        static::$default = array_merge(static::$default, $default);
    }

    /**
     * 获取参数
     *
     * @return array
     */
    public function getParams(?array $keys = NULL): array
    {
		if(NULL === $keys) 
			return $this->params;
		
		$bind = [];
		foreach ($keys as $k)
        {		
            foreach ($this->params[$k] as $param) 
			{
				!empty($param) && $bind = array_merge($bind, $param);
            }
	    }

        return $bind;
    }

    /**
     * 获取查询字串属性值
     *
     * @access public
     * @param string|null $name 属性名称
     * @return mixed
     */
    public function getQuery(?string $name = NULL, $default = NULL)
    {
		if(NULL === $name) 
			return $this->sql;
		
        return isset($this->sql[$name]) 
		    ? $this->sql[$name] 
			: $default;
    }

    /**
     * 清除查询字串属性值
     *
     * @access public
     * @param string $attributeName 属性名称
     * @return Query
     */
    public function removeQuery(string $name): Query
    {
        if (isset($this->sql[$name])) {
			
            $this->sql[$name] = static::$default[$name];

			$this->params[$name] = [];
        }
        return $this;
    }

    /**
     * 查询的表
     *
     * @param string $table 查询的表
     * @return $this
     */
    public function from(string $table): Query
    {
		$this->sql['table'] = $this->prefix($table);
        return $this;
    }

    /**
     * 过滤表前缀,表前缀由table.构成
     *
     * @param string $string 需要解析的字符串
     * @return string
     */
    public function prefix(string $string)
    {
		return (false !== strpos($string, 'table.')) ? str_replace('table.', $this->prefix, $string) : $string;
    }

    /**
     * 从参数中合成查询字段
     *
     * @access private
     * @param array $parameters
     * @return string
     */
    private function getColumnFromParameters(array $parameters)
    {
        $fields = [];
        foreach ($parameters as $value) 
		{
            if (is_array($value)) {
                foreach ($value as $key => $val) {
                    $fields[] = $key . ' AS ' . $val;
                }
            } else {
                 $fields[] = $value;
            }
        }

        return $this->filter(implode(' , ', $fields));
    }

    /**
     * 选择查询字段
     * usage: select('id', 'age') || select('gradescore as score') || select('sum(user.score) num')
     * @access public
     * @param mixed $field 查询字段
     * @return $this
     */
    public function select(...$args): Query
    {
		$this->sql['action'] = 'SELECT';
		$this->sql['fields'] = $this->getColumnFromParameters($args);
        return $this;
    }

    /**
     * 连接表   user1 inner join user2 on user1.user_name=user2.user_name;
     * usage: join('article as a on u.id=a.uid') ||
     * join('role on test_user.role_id=test_role.id and test_role.status>?', INNER, [1])
     * @param string $table 需要连接的表
     * @param string $condition 连接条件
     * @param string $op 连接方法(LEFT, RIGHT, INNER)
     * @param ...$values
     * @return Query
     */
    public function join(string $table, string $condition, string $op = 'INNER', ...$values): Query
    {
		$table = $this->prefix($table);
		$condition = $this->filter($condition);
		$this->sql['join'] .= " {$op} JOIN {$table} ON {$condition}";
		
		$values && $this->params['join'][] = is_array($values[0]) ? $values[0] : $values;
        return $this;
    }

    /**
     * HAVING (HAVING)
     *
     * usage: having('num > ?', [2]) || having('uid > ? and num > ?', [8, 2])
     * @param string $condition 查询条件
     * @param mixed $param 条件值
     * @return $this
     */
    public function having(string $condition, ...$values): Query
    {
		$operator = empty($this->sql['having']) ? ' HAVING ' : ' AND';
        $this->sql['having'] .= $operator . ' (' . $this->filter($condition) . ')';
		
		$values && $this->params['having'][] = is_array($values[0]) ? $values[0] : $values;
		
        return $this;
    }

    /**
     * 条件查询语句:  [字段名 => (值, 操作, 连接运算符, 值是否是SQL命令), ...]
     * usage: where('id = ?', 2) || where('id = ? or age > ?', 2, 24) || where('id > ?', [45]) , where('id > ?', 45)
     * @param string $condition 查询条件
     * @param mixed $param 条件值
     * @return Query
     */
    public function where(string $condition, ...$values): Query
    {
		$operator = empty($this->sql['where']) ? ' WHERE ' : ' AND';
        $this->sql['where'] .= $operator . ' (' . $this->filter($condition) . ')';
		
		$values && $this->params['where'][] = is_array($values[0]) ? $values[0] : $values;

		return $this;
    }

    /**
     * OR条件查询语句
     *
     * @param string $condition 查询条件
     * @param mixed $param 条件值
     * @return Query
     */
    public function orWhere(string $condition, ...$values): Query
    {
		$operator = empty($this->sql['where']) ? ' WHERE ' : ' OR';
        $this->sql['where'] .= $operator . ' (' . $this->filter($condition) . ')';
		
		$values && $this->params['where'][] = is_array($values[0]) ? $values[0] : $values;
		return $this;
    }

    /**
     * 排序顺序(ORDER BY)  
     * 
     * @param string $orderby 排序的索引
     * @param string $sort 排序的方式(ASC, DESC, asc, desc)
     * @access public
     * @return Query
     */
    public function order(string $orderby, string $sort = 'ASC'): Query
    {
		$operator = empty($this->sql['order']) ? ' ORDER BY ' : ' , ';
        $this->sql['order'] .= $operator . $this->filter($orderby) . (!empty($sort) && false !== stripos('|ASC|DESC|', '|' . $sort . '|') ? ' ' . $sort : NULL);

		return $this;
    }
	
    /**
     * 查询行数限制
     *
     * @param integer $limit 需要查询的行数
     * @return Query
     */
    public function limit($limit): Query
    {
		$this->sql['limit'] = abs(intval($limit));
        return $this;
    }

    /**
     * 查询行数偏移量
     *
     * @param integer $offset 需要偏移的行数
     * @return Query
     */
    public function offset($offset): Query
    {
		$this->sql['offset'] = abs(intval($offset));
        return $this;
    }

    /**
     * 分页查询
     *
     * @param integer $page 页数
     * @param integer $pageSize 每页行数
     * @return Query
     */
    public function page(int $page, int $pageSize): Query
    {
        return $this->limit($pageSize)
		    ->offset((max(intval($page), 1) - 1) * $pageSize);
    }
	
    /**
     * 集合聚集(GROUP BY)
     * usage: group('username') || groupBy('username ?', 'age')
     * @param string $column 聚集的键值
     * @return Query
     */
    public function group(string $column, ...$values): Query
    {
		$this->sql['group'] = $this->filter($column);
		
		$values && $this->params['group'][] = is_array($values[0]) ? $values[0] : $values;
		
        return $this;
    }

    /**
     * 插入记录操作(INSERT)
     *
     * @param array $row 指定需要写入的栏目及其值 
     * @return $this
     */
    public function insert(string $table): Query
    {
		$this->sql['action'] = 'INSERT';
		$this->sql['table'] = $this->prefix($table);

        return $this;
    }

    /**
     * 指定需要写入栏目及其值
     * 单行且不会转义引号
     *
     * @param string $key 栏目名称
     * @param mixed $value 指定的值
     * @param bool $escape 是否转义
     * @return Query
     */
    public function expression(string $key, $value): Query
    {
		$this->sql['rows'][$key] = ['row'=>is_null($value) ? 'NULL' : $value, 'escape_key'=>$this->filter($key), 'escape'=>true];
        return $this;
    }

    /**
     * 更新记录操作(UPDATE)
     *
     * @param array $rows 指定需要写入的栏目及其值 
     * @return $this
     */
    public function update(string $table): Query
    {
		$this->sql['action'] = 'UPDATE';
		$this->sql['table'] = $this->prefix($table);
        return $this;
    }

    /**
     * 指定需要写入的栏目及其值
     *
     * @param array $rows
     * @return Query
     */
    public function rows(array $rows): Query
    {
        foreach ($rows as $key => $row) 
		{
            $this->sql['rows'][$key] = ['row'=>is_null($row) ? 'NULL' : $row, 'escape_key'=>$this->filter($key), 'escape'=>false];
        }
        return $this;
    }


    /**
     * 删除记录操作(DELETE)
     *
     * @param array $values 指定需要写入的栏目及其值 
     * @return $this
     */
    public function delete(string $table): Query
    {
		$this->sql['action'] = 'DELETE';
		$this->sql['table'] = $this->prefix($table);
        return $this;
    }

    /**
     * 直接Sql语句查询
     * 
     * @param string $sql           
     * @param mixed $parameter          
     * @return mixed
     */
    public function sql(string $sql, ...$values): Query
    {
		$this->sql['action'] = 'SQL';
		$this->sql['sql'] = $this->prefix($sql);
		$values && $this->params['sql'][] = is_array($values[0]) ? $values[0] : $values;
		
        return $this;
    }

    /**
     * 查询一条记录
	 *
     * @return array
     */
    public function fetch(array $filter = NULL)
	{
		NULL === $this->sql['limit'] && $this->sql['limit'] = 1;
        return $this->db->adapter()->fetch($this, $filter);
    }

    /**
     * 查询多条记录
	 *
     * @return array
     */
    public function get(array $filter = NULL)
	{
        return $this->db->adapter()->get($this, $filter);
    }

    /**
     * 查询一条记录
	 *
     * @return array
     */
    public function object(array $filter = NULL)
	{
		NULL === $this->sql['limit'] && $this->sql['limit'] = 1;
        return $this->db->adapter()->object($this, $filter);
    }

    /**
     * 对数据库查询运行计数函数
     *
     * @return int
     */
    public function count()
	{
        return $this->db->adapter()->count($this);
    }

	/**
     * 
	 * 
	 * @return	int
     */
	public function affected() 
	{
		return $this->db->adapter()->affected($this);
	}

	/**
     * 
	 * 
	 * @return	int
     */
	public function lastId()
	{
		return $this->db->adapter()->lastId($this);
	}
}
